# @Project: diablo
# @File: stock_related_price_migrate
# @Time: 2023/11/17 15:28
# @User: Jolly
# @Email: jolly@007vin.com

"""
新架构库存定价迁移
"""

import datetime
import time
import threading
import requests
from queue import Queue

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB


class StockRelatedPriceMigrate:

    def __init__(self):

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=150,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 100  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def get_stock_related(self):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id in
                          ('1390f84351fe464b90dcdbf3a164ff24', '3') """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        # url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    def get_goods_quality_data(self, merchant_id):
        """
        获取商品品质数据
        """

        quality_list = []
        quality_replace = {}

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select ori_quality, quality from diablo_quality_replace where merchant_id='{merchant_id}' and status=1"""

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    quality_list.append({
                        "ori_quality": result_[0],
                        "quality": result_[1]
                    })

                quality_replace = {i.get('ori_quality', ''): i.get('quality', '') for i in quality_list}

        except Exception as e:
            print("获取品质信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return quality_replace

    def get_agreement_stock_data(self, erp_type, erp_uid, oe, ori_quality):
        """
        获取原库存数据
        """

        stocks_identify = []

        if oe:
            storage_mongo = self.diablo_conn_2_0

            try:
                if storage_mongo is not None:

                    collection_name = f'{erp_type}_meta_stocks'

                    # 根据erp厂家动态获取mongo客户端对象
                    conn = getattr(storage_mongo.inventory_data, collection_name)

                    filter_dict = {
                        "opid": oe,
                        "ori_quality": ori_quality,
                        "erp_uid": erp_uid
                    }
                    docs = conn.find(filter_dict, {'identify': 1})

                    for doc in docs:
                        identify_dict = {'identify': doc.get("identify", "")}

                        stocks_identify.append(identify_dict)

            except Exception as e:
                print("获取用户库存数据identify失败 - mongo：", e)
            finally:
                # 将连接放回连接池
                storage_mongo.close()

        return stocks_identify

    def get_merchant_agreement_excel(self, merchant_id):
        """
        获取商家价格模板列表
        """

        agreement_info_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"select id, agreement_name from diablo_excel_agreement where merchant_id='{merchant_id}' and status=1"

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    agreement_info_list.append({
                        "agreement_excel_id": result_[0],
                        "agreement_name": result_[1]
                    })

        except Exception as e:
            print("获取价格模板信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return agreement_info_list

    def get_agreement_price(self, agreement_excel_id, identify):
        """
        获取原库存定价数据
        """

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:

                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_excel"]

                filter_dict = {
                    "identify": identify,
                    "agreement_excel_id": agreement_excel_id
                }

                docs = conn.find(filter_dict).sort("update_time", -1)

                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    for doc in docs:
                        return doc  # 按照时间降序取第一个

                    # {
                    #     "_id": ObjectId("6551ab6a35b4d63c7f20b7cd"),
                    #     "agreement_excel_id": NumberInt(33),
                    #     "identify": "08a75bd88aba05ff39e599a68e01d63c",
                    #     "agreement_price": NumberInt(205),
                    #     "price_type": "customize",
                    #     "price_value": NumberInt(205),
                    #     "price_desc": "¥ = 205",
                    #     "update_time": "2023-11-13 12:51:54",
                    #     "user_id": "0000000000000000003",
                    #     "price_user": "系统人工控价",
                    #     "price_time": "2023-11-13"
                    # }

                else:
                    return {}

        except Exception as e:
            print("获取原库存定价失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def culculate_price_rate(self, _sale_price, _agreement_price, _price_type):
        """
        计算原定价相比现有商品库存价上涨比例，并返回

        计算新的加价比例
        _sale_price: 库存价
        _agreement_price: 新协议价
        _price_type: 加价类型
        """
        if isinstance(_agreement_price, str):
            _agreement_price = float(_agreement_price)
        if isinstance(_sale_price, str):
            _sale_price = float(_sale_price)
        if _sale_price <= 0:
            return 0
        if _price_type == 'percent_sub':  # 降价
            _price_value = round(1 - _agreement_price / _sale_price, 4)
        elif _price_type == 'percent_add':  # 加价
            _price_value = round(_agreement_price / _sale_price - 1, 4)
        elif _price_type == 'selected_add':  # 定价 加价
            _price_value = round(_agreement_price - _sale_price, 4)
        elif _price_type == 'selected_sub':  # 定价 降价
            _price_value = round(_sale_price - _agreement_price, 4)
        elif _price_type == 'customize':  # 固定 价格
            _price_value = _agreement_price
        else:
            _price_value = 0

        return _price_value

    def public_build_price_desc(self, sale_price, price_type, price_value):
        """拼接加价规则描述"""
        if isinstance(sale_price, str):
            sale_price = float(sale_price)
        if isinstance(price_value, str):
            price_value = float(price_value)
        if price_type == 'percent_sub':  # 降价
            return f'¥ = {sale_price}*{round((1 - price_value) * 100, 2)}%'
        elif price_type == 'percent_add':  # 加价
            return f'¥ = {sale_price}*{round((1 + price_value) * 100, 2)}%'
        elif price_type == 'selected_add':  # 定价 加价
            return f'¥ = {sale_price}+{price_value}'
        elif price_type == 'selected_sub':  # 定价 降价
            return f'¥ = {sale_price}-{price_value}'
        elif price_type == 'customize':  # 固定 价格
            return f'¥ = {price_value}'
        return ''

    # 判断定价协议表是否已有对应数据，有的话就删除
    def check_agreement_exist(self, code, agreement_excel_id, merchant_id):

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:
                    # 删除已有的
                    conn.delete_many(filter_dict)
                    # check = True

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check

    # 获取新库存价格
    def get_stock_price(self, code, merchant_id):

        # show_fields.update({
        #     'code': 1,
        #     '_id': 0
        # })
        # filter_dict = {
        #     'merchant_id': merchant_id,
        # }
        # if goods_codes:
        #     filter_dict.update({
        #         'code': {"$in": goods_codes}
        #     })
        # if no_stock_price:
        #     filter_dict.update({
        #         'stock_price': {"$eq": None},
        #     })
        # result = self.db.goods.find(filter_dict, show_fields)

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:

                # 执行数据库操作
                db = connection["ddll"]
                conn = db["goods"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "code": code
                }

                docs = conn.find(filter_dict, {"stock_price": 1})

                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    for doc in docs:
                        if not doc.get("stock_price"):
                            continue
                        else:
                            return doc

                    # {
                    #     "_id": ObjectId("6551ab6a35b4d63c7f20b7cd"),
                    #     "agreement_excel_id": NumberInt(33),
                    #     "identify": "08a75bd88aba05ff39e599a68e01d63c",
                    #     "agreement_price": NumberInt(205),
                    #     "price_type": "customize",
                    #     "price_value": NumberInt(205),
                    #     "price_desc": "¥ = 205",
                    #     "update_time": "2023-11-13 12:51:54",
                    #     "user_id": "0000000000000000003",
                    #     "price_user": "系统人工控价",
                    #     "price_time": "2023-11-13"
                    # }

                else:
                    return {}

        except Exception as e:
            print("获取原库存定价失败2 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 查找库存关联表查找有效的库存关系

    # 循环遍历多条关系记录，根据记录中的厂商通id查找商品数据，全部拉取回来

    # 循环商品数据，如果库存价格为空，则跳过，存在的话则根据每条商品的品质和oe，结合库存关系的uid，erp_type从对应的原库存中查找对应库存记录，
    # 得到identify后查找agreement记录，如果没有agreement记录则跳过，有则取出定价类型，定价，计算定价对比库存价的涨幅，形成涨幅比例，将整理
    # 好的定价写入新表

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]

        erp_type = stock_related.get("erp_type")
        erp_uid = stock_related.get("erp_uid")
        code = goods.get("code")
        oe = goods.get("standard_code")
        ori_quality = goods.get("brand_names")

        is_online = goods.get("is_online")
        if not is_online:
            print(f"商品无库存，已下线")
            return

        # 获取库存价格
        stock_doc = self.get_stock_price(code, stock_related.get("merchant_id"))
        if not stock_doc:
            print(f"库存价格为空----{stock_related.get('merchant_id')}----{code}")
            return

        stock_price = stock_doc.get("stock_price")
        goods.update({"stock_price": stock_price})

        # # 如果库存价格为空，则return
        # if not goods.get("stock_price"):
        #     print("库存价格为空")
        #     return

        # oe、ori_quality不允许为空
        if not oe or not ori_quality:
            print(f"oe、ori_quality为空 --------{oe}--------{ori_quality}")
            return

        # 获取库存数据
        stocks_identify = self.get_agreement_stock_data(erp_type, erp_uid, oe, ori_quality)
        if not stocks_identify:
            print(f"库存数据为空------{oe}------{erp_type}------{erp_uid}------{ori_quality}")
            return

        # 遍历当前用户所有价格模板，查询所有id，然后分id查询价格并最终写入新的库存价格表
        if stock_related.get('merchant_id') == "1390f84351fe464b90dcdbf3a164ff24":
            agreement_info_list = self.get_merchant_agreement_excel("3")
        else:
            agreement_info_list = self.get_merchant_agreement_excel(stock_related.get("merchant_id"))
        if not agreement_info_list:
            print(f"价格模板为空---------{stock_related.get('merchant_id')}")
            return

        for agreement_info in agreement_info_list:

            agreement_excel_id = agreement_info.get("agreement_excel_id")

            # 查找定价记录
            agreement_price_data = self.get_agreement_price(agreement_excel_id, stocks_identify[0].get("identify"))

            if not agreement_price_data:
                print(f"定价记录为空---------{agreement_excel_id}-------------{stocks_identify[0].get('identify')}")
                continue

            # 库存价和定价计算涨幅，如果定价小于库存价则在库存价的基础上上调20%
            stock_price = goods.get("stock_price")  # 库存价
            agreement_price = agreement_price_data.get("agreement_price")

            # 如果定价小于库存价，则以定价金额直接定价
            if agreement_price <= stock_price:
                price_type = "customize"
            else:
                price_type = "percent_add"

            # 计算涨幅
            up_rate = self.culculate_price_rate(stock_price, agreement_price, price_type)
            # 拼接描述
            price_desc = self.public_build_price_desc(stock_price, price_type, up_rate)

            # 检查新表是否存在库存
            check = self.check_agreement_exist(code, agreement_excel_id, stock_related.get("merchant_id"))
            if not check:

                agreement_dict = {
                    "agreement_excel_id": agreement_excel_id,
                    "code": code,
                    "merchant_id": stock_related.get("merchant_id"),
                    "agreement_price": agreement_price,
                    "price_type": price_type,
                    "price_value": up_rate,
                    "price_desc": price_desc,
                    "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                    "user_id": "0000000000000000003",  # 当前用户id
                    "price_user": "系统人工控价",  # 当前用户名
                    "price_time": datetime.datetime.now().strftime('%Y-%m-%d')
                }

                # 获取2.0MongoDB连接
                connection = self.diablo_conn_2_0

                try:
                    if connection is not None:
                        # 执行数据库操作
                        db = connection["inventory_data"]
                        conn = db["agreement_goods_price"]
                        conn.insert_one(dict(agreement_dict))

                except Exception as e:
                    print("写入2.0MongoDB数据库单据失败：", e)
                finally:
                    # 将连接放回连接池
                    connection.close()

    # 任务执行
    def task(self):
        q = Queue(maxsize=100)  # 设定最大队列数和线程数

        count = 1

        # 获取所有库存记录
        stock_related_list = self.get_stock_related()

        for stock_related in stock_related_list:

            goods_cst_id = stock_related.get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            for goods in goods_list:

                t = threading.Thread(target=self.write_data, args=(goods, stock_related))
                q.put(t)
                if q.full() == True:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("当前写入数：", count,
                      ", 商家为：", stock_related.get("merchant_name"),
                      ", 商品编码为：", goods.get("code"),
                      ", 名称为：", goods.get("part_name"))

                count += 1

            # 处理残余队列数据
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()


if __name__ == "__main__":
    StockRelatedPriceMigrate()